/*
 Navicat Premium Data Transfer

 Source Server         : demo
 Source Server Type    : MySQL
 Source Server Version : 80031
 Source Host           : localhost:3306
 Source Schema         : day19-month14

 Target Server Type    : MySQL
 Target Server Version : 80031
 File Encoding         : 65001

 Date: 09/06/2023 14:49:43
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for account
-- ----------------------------
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `ID` int NOT NULL COMMENT '编号',
  `UID` int DEFAULT NULL COMMENT '用户编号',
  `MONEY` double DEFAULT NULL COMMENT '金额',
  PRIMARY KEY (`ID`),
  KEY `FK_Reference_8` (`UID`),
  CONSTRAINT `FK_Reference_8` FOREIGN KEY (`UID`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of account
-- ----------------------------
BEGIN;
INSERT INTO `account` (`ID`, `UID`, `MONEY`) VALUES (1, 41, 1000);
INSERT INTO `account` (`ID`, `UID`, `MONEY`) VALUES (2, 45, 1000);
INSERT INTO `account` (`ID`, `UID`, `MONEY`) VALUES (3, 41, 2000);
COMMIT;

-- ----------------------------
-- Table structure for danwei
-- ----------------------------
DROP TABLE IF EXISTS `danwei`;
CREATE TABLE `danwei` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of danwei
-- ----------------------------
BEGIN;
INSERT INTO `danwei` (`id`, `name`) VALUES (4, '强生');
INSERT INTO `danwei` (`id`, `name`) VALUES (14, '强生111');
INSERT INTO `danwei` (`id`, `name`) VALUES (13, '强生122');
INSERT INTO `danwei` (`id`, `name`) VALUES (12, '强生133');
INSERT INTO `danwei` (`id`, `name`) VALUES (11, '强生144');
INSERT INTO `danwei` (`id`, `name`) VALUES (10, '强生155');
INSERT INTO `danwei` (`id`, `name`) VALUES (9, '强生166');
INSERT INTO `danwei` (`id`, `name`) VALUES (8, '强生177');
INSERT INTO `danwei` (`id`, `name`) VALUES (7, '强生188');
INSERT INTO `danwei` (`id`, `name`) VALUES (6, '强生199');
INSERT INTO `danwei` (`id`, `name`) VALUES (5, '强生1999');
INSERT INTO `danwei` (`id`, `name`) VALUES (2, '生物');
INSERT INTO `danwei` (`id`, `name`) VALUES (1, '科兴');
INSERT INTO `danwei` (`id`, `name`) VALUES (3, '辉瑞');
COMMIT;

-- ----------------------------
-- Table structure for house
-- ----------------------------
DROP TABLE IF EXISTS `house`;
CREATE TABLE `house` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '楼盘名称',
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '楼盘地址',
  `province_id` int DEFAULT NULL COMMENT '省id',
  `city_id` int DEFAULT NULL COMMENT '市id',
  `pic_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '图片地址',
  `area` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '建筑面积',
  `sms` int DEFAULT '0' COMMENT '是否已发送短信  0否 1是',
  `qu` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',
  PRIMARY KEY (`id`),
  KEY `idx_region` (`province_id`,`city_id`,`qu`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Records of house
-- ----------------------------
BEGIN;
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`, `qu`) VALUES (1, '碧桂园小区', '和平路1号', 1, 4, 'https://foruda.gitee.com/avatar/1683537254300569894/12065236_hccxx_1683537254.png', '1000', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`, `qu`) VALUES (2, '保利小区', '健康路1号', 2, 9, 'https://foruda.gitee.com/avatar/1683537254300569894/12065236_hccxx_1683537254.png', '2000', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`, `qu`) VALUES (3, '融创小区', '胜利路1号', 3, 13, 'https://foruda.gitee.com/avatar/1683537254300569894/12065236_hccxx_1683537254.png', '3000', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`, `qu`) VALUES (4, '绿地小区', '建国路1号', 3, 14, 'https://foruda.gitee.com/avatar/1683537254300569894/12065236_hccxx_1683537254.png', '3500', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`, `qu`) VALUES (5, '绿地小区3区', '建国路1号', 3, 15, 'https://foruda.gitee.com/avatar/1683537254300569894/12065236_hccxx_1683537254.png', '3500', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`, `qu`) VALUES (6, '绿地小区2区', '建国路1号', 3, 15, 'https://foruda.gitee.com/avatar/1683537254300569894/12065236_hccxx_1683537254.png', '3500', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`, `qu`) VALUES (7, '绿地小区4区', '建国路1号', 3, 16, NULL, '3500', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`, `qu`) VALUES (8, '绿地小区5区', '建国路1号', 3, 16, NULL, '3500', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`, `qu`) VALUES (9, '绿地小区6区', '建国路1号', 3, 17, NULL, '3500', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`, `qu`) VALUES (10, '万达小区1号', '晋城高平市', 3, 17, 'https://foruda.gitee.com/avatar/1683537254300569894/12065236_hccxx_1683537254.png', '200', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`, `qu`) VALUES (11, 'dddd', 'ddddd', 1, 4, 'https://foruda.gitee.com/avatar/1683537254300569894/12065236_hccxx_1683537254.png', '200', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`, `qu`) VALUES (12, '123456', '123456', 1, 4, 'https://foruda.gitee.com/avatar/1683537254300569894/12065236_hccxx_1683537254.png', NULL, NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`, `qu`) VALUES (13, '45678', '45678', 2, 10, 'https://foruda.gitee.com/avatar/1683537254300569894/12065236_hccxx_1683537254.png', NULL, NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`, `qu`) VALUES (14, 'cc hu', 'tianjin', 1, 4, 'http://localhost:8080/static/e755206e-b521-4e0e-8d53-32787b962464saber.jpeg', NULL, NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`, `qu`) VALUES (15, 'cc hu', 'tianjin', 1, 7, 'http://localhost:8080/static/3acda1bb-9ae9-4e23-a5b4-ee4e27aac91dtest01.jpg', '200', 0, '城西');
COMMIT;

-- ----------------------------
-- Table structure for nation
-- ----------------------------
DROP TABLE IF EXISTS `nation`;
CREATE TABLE `nation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of nation
-- ----------------------------
BEGIN;
INSERT INTO `nation` (`id`, `name`) VALUES (1, '中国');
INSERT INTO `nation` (`id`, `name`) VALUES (2, '美国');
INSERT INTO `nation` (`id`, `name`) VALUES (3, '韩');
INSERT INTO `nation` (`id`, `name`) VALUES (4, '日');
INSERT INTO `nation` (`id`, `name`) VALUES (5, '朝鲜');
INSERT INTO `nation` (`id`, `name`) VALUES (6, '加');
INSERT INTO `nation` (`id`, `name`) VALUES (7, '澳大');
COMMIT;

-- ----------------------------
-- Table structure for region
-- ----------------------------
DROP TABLE IF EXISTS `region`;
CREATE TABLE `region` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '名称',
  `parent_id` int DEFAULT NULL COMMENT '父级id',
  `level` int DEFAULT NULL COMMENT '0 代表省,  1代表市',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Records of region
-- ----------------------------
BEGIN;
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (1, '河北省', NULL, 0);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (2, '山东省', NULL, 0);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (3, '山西省', NULL, 0);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (4, '廊坊市', 1, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (5, '邢台市', 1, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (6, '唐山市', 1, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (7, '石家庄市', 1, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (8, '邯郸市', 1, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (9, '济南市', 2, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (10, '潍坊市', 2, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (11, '青岛市', 2, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (12, '菏泽市', 2, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (13, '太原市', 3, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (14, '阳泉市', 3, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (15, '吕梁市', 3, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (16, '大同市', 3, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (17, '晋城市', 3, 1);
COMMIT;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT '用户名称',
  `birthday` datetime DEFAULT NULL COMMENT '生日',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  `address` varchar(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
INSERT INTO `user` (`id`, `username`, `birthday`, `sex`, `address`) VALUES (41, '老王', '2018-02-27 17:47:08', '男', '北京');
INSERT INTO `user` (`id`, `username`, `birthday`, `sex`, `address`) VALUES (42, '小二王', '2018-03-02 15:09:37', '女', '北京');
INSERT INTO `user` (`id`, `username`, `birthday`, `sex`, `address`) VALUES (43, '小二王', '2018-03-04 11:34:34', '女', '北京金燕龙');
INSERT INTO `user` (`id`, `username`, `birthday`, `sex`, `address`) VALUES (45, '八维', '2018-03-04 12:04:06', '男', '北京金燕龙');
INSERT INTO `user` (`id`, `username`, `birthday`, `sex`, `address`) VALUES (46, '老王', '2018-03-07 17:37:26', '男', '北京');
INSERT INTO `user` (`id`, `username`, `birthday`, `sex`, `address`) VALUES (48, '小马宝莉', '2018-03-08 11:44:00', '女', '北京修正');
COMMIT;

-- ----------------------------
-- Table structure for user_info
-- ----------------------------
DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `phone` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `salt` varchar(255) DEFAULT NULL,
  `nick_name` varchar(255) DEFAULT NULL,
  `avatar_url` varchar(255) DEFAULT NULL,
  `gitee_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of user_info
-- ----------------------------
BEGIN;
INSERT INTO `user_info` (`id`, `phone`, `password`, `salt`, `nick_name`, `avatar_url`, `gitee_id`) VALUES (2, '123', 'e2e579d92595844df5c8f8376811a09e', '004b0518', 'Aikun', 'https://foruda.gitee.com/avatar/1685610497471368994/12065236_hccxx_1685610497.png', 12065236);
COMMIT;

-- ----------------------------
-- Table structure for yimiao
-- ----------------------------
DROP TABLE IF EXISTS `yimiao`;
CREATE TABLE `yimiao` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name_id` int DEFAULT NULL COMMENT '国家id',
  `danwei_id` int DEFAULT NULL COMMENT '单位id',
  `total_count` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '累计接种次数',
  `bai_person` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '每百人接种',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of yimiao
-- ----------------------------
BEGIN;
INSERT INTO `yimiao` (`id`, `name_id`, `danwei_id`, `total_count`, `bai_person`, `update_time`) VALUES (1, 1, 1, '100.5', '20.5', '2023-04-13 14:49:30');
INSERT INTO `yimiao` (`id`, `name_id`, `danwei_id`, `total_count`, `bai_person`, `update_time`) VALUES (2, 2, 3, '50.5', '10.5', NULL);
INSERT INTO `yimiao` (`id`, `name_id`, `danwei_id`, `total_count`, `bai_person`, `update_time`) VALUES (4, 3, 4, '10', '2', NULL);
INSERT INTO `yimiao` (`id`, `name_id`, `danwei_id`, `total_count`, `bai_person`, `update_time`) VALUES (7, 4, 4, '50', NULL, NULL);
INSERT INTO `yimiao` (`id`, `name_id`, `danwei_id`, `total_count`, `bai_person`, `update_time`) VALUES (8, 5, 1, '100', '20', NULL);
INSERT INTO `yimiao` (`id`, `name_id`, `danwei_id`, `total_count`, `bai_person`, `update_time`) VALUES (9, 6, 3, '100', NULL, NULL);
INSERT INTO `yimiao` (`id`, `name_id`, `danwei_id`, `total_count`, `bai_person`, `update_time`) VALUES (10, 7, 4, '20', NULL, NULL);
INSERT INTO `yimiao` (`id`, `name_id`, `danwei_id`, `total_count`, `bai_person`, `update_time`) VALUES (12, 1, 2, '100', '1', NULL);
COMMIT;

-- ----------------------------
-- Procedure structure for zxt
-- ----------------------------
DROP PROCEDURE IF EXISTS `zxt`;
delimiter ;;
CREATE PROCEDURE `zxt`()
BEGIN
    select nation.name as xdata,
	total_count as ydata 
	from yimiao
	LEFT JOIN nation on yimiao.name_id = nation.id;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for zxtWithId
-- ----------------------------
DROP PROCEDURE IF EXISTS `zxtWithId`;
delimiter ;;
CREATE PROCEDURE `zxtWithId`(IN d int)
BEGIN
  select nation.name as xdata,
	yimiao.total_count as ydata 
	from yimiao
	LEFT JOIN nation on yimiao.name_id = nation.id
	
	where yimiao.danwei_id = d;
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;
